const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function migrateGemBase() {
    let connection;

    try {
        console.log('🔄 开始迁移gem_base表到新结构...');

        // 连接数据库
        connection = await mysql.createConnection(dbConfig);

        // 1. 备份现有数据
        console.log('📋 备份现有gem_base数据...');
        const [existingData] = await connection.execute('SELECT * FROM gem_base');
        console.log(`✅ 备份了 ${existingData.length} 条记录`);

        // 2. 删除外键约束
        console.log('🔗 删除外键约束...');
        try {
            await connection.execute('ALTER TABLE character_gem_inventory DROP FOREIGN KEY fk_character_gem_inventory_gem_base');
            console.log('✅ 删除character_gem_inventory外键约束成功');
        } catch (error) {
            console.log('⚠️ 外键约束可能不存在，继续执行...');
        }

        try {
            await connection.execute('ALTER TABLE equipment_gem_socket DROP FOREIGN KEY fk_equipment_gem_socket_gem_base');
            console.log('✅ 删除equipment_gem_socket外键约束成功');
        } catch (error) {
            console.log('⚠️ 外键约束可能不存在，继续执行...');
        }

        // 3. 删除现有表
        console.log('🗑️ 删除现有gem_base表...');
        await connection.execute('DROP TABLE IF EXISTS gem_base');

        // 3. 创建新表结构
        console.log('🏗️ 创建新的gem_base表结构...');
        const createTableSQL = `
        CREATE TABLE \`gem_base\` (
          \`id\` int NOT NULL AUTO_INCREMENT COMMENT '宝石ID',
          \`name\` varchar(50) NOT NULL COMMENT '宝石名称',
          \`image\` varchar(100) DEFAULT NULL COMMENT '宝石图片',
          \`max_level\` int NOT NULL DEFAULT 10 COMMENT '最大等级',
          \`equipment_slots\` json NOT NULL COMMENT '可镶嵌的装备槽位',
          \`possible_attributes\` json NOT NULL COMMENT '可能的属性列表，生成时随机选择',
          \`created_at\` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
          \`updated_at\` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
          PRIMARY KEY (\`id\`),
          UNIQUE KEY \`uk_name\` (\`name\`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='宝石基础表';
        `;
        await connection.execute(createTableSQL);

        // 4. 插入新的宝石数据
        console.log('💎 插入新的宝石数据...');
        const insertSQL = `
        INSERT INTO \`gem_base\` (\`name\`, \`image\`, \`max_level\`, \`equipment_slots\`, \`possible_attributes\`) VALUES
        -- 钻石：抗物理、暴击率、速度
        ('钻石', 'diamond.png', 10, '["helmet", "necklace", "boots"]', 
         '[{"type": 1, "name": "抗物理", "maxValue": 15}, {"type": 7, "name": "暴击率", "maxValue": 15}, {"type": 15, "name": "速度", "maxValue": 15}]'),

        -- 水晶：抗妖火、力量、抗物理
        ('水晶', 'crystal.png', 10, '["weapon", "bracer"]', 
         '[{"type": 11, "name": "抗妖火", "maxValue": 15}, {"type": 18, "name": "力量", "maxValue": 15}, {"type": 1, "name": "抗物理", "maxValue": 15}]'),

        -- 玛瑙：抗落雷、敏捷、攻击
        ('玛瑙', 'agate.png', 10, '["necklace", "boots"]', 
         '[{"type": 3, "name": "抗落雷", "maxValue": 15}, {"type": 15, "name": "敏捷", "maxValue": 15}, {"type": 13, "name": "攻击", "maxValue": 750}]'),

        -- 白玉：抗毒术、气血、防御
        ('白玉', 'white_jade.png', 10, '["helmet", "bracer"]', 
         '[{"type": 4, "name": "抗毒术", "maxValue": 15}, {"type": 17, "name": "气血", "maxValue": 1500}, {"type": 14, "name": "防御", "maxValue": 750}]'),

        -- 琥珀：反震率、精力、抗围困
        ('琥珀', 'amber.png', 10, '["necklace", "armor"]', 
         '[{"type": 5, "name": "反震率", "maxValue": 15}, {"type": 18, "name": "精力", "maxValue": 1500}, {"type": 8, "name": "抗围困", "maxValue": 15}]'),

        -- 翡翠：躲避率、攻击、抗扰乱
        ('翡翠', 'jade.png', 10, '["armor", "boots"]', 
         '[{"type": 6, "name": "躲避率", "maxValue": 15}, {"type": 13, "name": "攻击", "maxValue": 750}, {"type": 9, "name": "抗扰乱", "maxValue": 15}]'),

        -- 珍珠：命中率、防御、抗封锁
        ('珍珠', 'pearl.png', 10, '["helmet", "necklace", "weapon", "bracer"]', 
         '[{"type": 19, "name": "命中率", "maxValue": 15}, {"type": 14, "name": "防御", "maxValue": 750}, {"type": 20, "name": "抗封锁", "maxValue": 15}]'),

        -- 绿宝石：抗围困、抗风沙、反击率
        ('绿宝石', 'emerald.png', 10, '["armor", "boots"]', 
         '[{"type": 8, "name": "抗围困", "maxValue": 15}, {"type": 21, "name": "抗风沙", "maxValue": 15}, {"type": 22, "name": "反击率", "maxValue": 15}]'),

        -- 红宝石：抗扰乱、抗妖火、致命率
        ('红宝石', 'ruby.png', 10, '["helmet", "weapon", "bracer"]', 
         '[{"type": 9, "name": "抗扰乱", "maxValue": 15}, {"type": 11, "name": "抗妖火", "maxValue": 15}, {"type": 23, "name": "致命率", "maxValue": 15}]'),

        -- 蓝宝石：抗封锁、抗落雷、体质
        ('蓝宝石', 'sapphire.png', 10, '["helmet", "armor"]', 
         '[{"type": 20, "name": "抗封锁", "maxValue": 15}, {"type": 3, "name": "抗落雷", "maxValue": 15}, {"type": 17, "name": "体质", "maxValue": 15}]'),

        -- 猫眼石：抗风沙、抗毒术、智力
        ('猫眼石', 'cat_eye.png', 10, '["necklace", "armor"]', 
         '[{"type": 21, "name": "抗风沙", "maxValue": 15}, {"type": 4, "name": "抗毒术", "maxValue": 15}, {"type": 16, "name": "智力", "maxValue": 15}]'),

        -- 金刚玉：暴击率、暴击力、穿透率
        ('金刚玉', 'adamantite_jade.png', 10, '["weapon", "bracer", "boots"]', 
         '[{"type": 7, "name": "暴击率", "maxValue": 15}, {"type": 24, "name": "暴击力", "maxValue": 15}, {"type": 25, "name": "穿透率", "maxValue": 15}]'),

        -- 龙血石：法爆率、爆伤力、暴击力
        ('龙血石', 'dragon_blood.png', 10, '["weapon", "bracer"]', 
         '[{"type": 26, "name": "法爆率", "maxValue": 12}, {"type": 27, "name": "爆伤力", "maxValue": 15}, {"type": 24, "name": "暴击力", "maxValue": 15}]'),

        -- 黑曜石：法爆率、法伤力、法穿率
        ('黑曜石', 'obsidian.png', 10, '["weapon", "bracer", "boots"]', 
         '[{"type": 26, "name": "法爆率", "maxValue": 12}, {"type": 28, "name": "法伤力", "maxValue": 10}, {"type": 29, "name": "法穿率", "maxValue": 10}]'),

        -- 乾坤珠：连击率、连击数、暴击率
        ('乾坤珠', 'qiankun_pearl.png', 10, '["weapon", "bracer"]', 
         '[{"type": 30, "name": "连击率", "maxValue": 15}, {"type": 31, "name": "连击数", "maxValue": 15}, {"type": 7, "name": "暴击率", "maxValue": 15}]');
        `;

        await connection.execute(insertSQL);

        // 5. 重新添加外键约束
        console.log('🔗 重新添加外键约束...');
        try {
            await connection.execute('ALTER TABLE character_gem_inventory ADD CONSTRAINT fk_character_gem_inventory_gem_base FOREIGN KEY (gemBaseId) REFERENCES gem_base(id) ON DELETE CASCADE');
            console.log('✅ 添加character_gem_inventory外键约束成功');
        } catch (error) {
            console.log('⚠️ 添加外键约束失败:', error.message);
        }

        try {
            await connection.execute('ALTER TABLE equipment_gem_socket ADD CONSTRAINT fk_equipment_gem_socket_gem_base FOREIGN KEY (gemBaseId) REFERENCES gem_base(id) ON DELETE CASCADE');
            console.log('✅ 添加equipment_gem_socket外键约束成功');
        } catch (error) {
            console.log('⚠️ 添加外键约束失败:', error.message);
        }

        // 6. 验证数据
        console.log('✅ 验证新数据...');
        const [newData] = await connection.execute('SELECT * FROM gem_base');
        console.log(`✅ 成功插入 ${newData.length} 条宝石记录`);

        // 6. 显示新数据
        console.log('\n📋 新的宝石数据:');
        newData.forEach((gem, index) => {
            console.log(`${index + 1}. ${gem.name} - 可镶嵌: ${gem.equipment_slots} - 属性: ${gem.possible_attributes.length}种`);
        });

        console.log('\n🎉 gem_base表迁移完成！');
        console.log('📝 新特性:');
        console.log('  - 每个宝石支持多种属性随机生成');
        console.log('  - 装备槽位映射已更新');
        console.log('  - 属性类型映射已更新');

    } catch (error) {
        console.error('❌ 迁移失败:', error.message);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// 运行迁移
migrateGemBase().catch(console.error);
